package com.cefa.edu.dao;

import com.cefa.edu.pojo.*;
import com.cefa.edu.pojo.Class;
import com.cefa.edu.pojo.Course;
import com.cefa.edu.pojo.Role;
import com.cefa.edu.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author huangzehui
 * @date 18-8-10 下午1:46
 */
@Mapper
public interface UserOrganRoleMapper {
    /**
     * @aa 根据学习阶段id 判断该阶段下是否有班级
     * @aa add by chaijian 2018-08-15
     */
    @Select({"select count(uor.class_id) from cefa_edu_user_organ_role uor where uor.state=0 and uor.phase_id=#{phaseId}"})
    int countUserOrganRoleByPhaseId(Integer phaseId);

    /**
     * @aa 根据班级id 判断指定角色的关系记录数
     * @aa add by chaijian 2018-08-15
     */
    @Select({"select count(uor.id) from cefa_edu_user_organ_role uor where uor.class_id=#{classId} and uor.state=0 and uor.role_type in(4,5,7)"})
    int countUserOrganRoleByClassIdAndRoleType(Integer classId);

    /**
     * @aa 根据年级id 获取该年级所在的学校阶段id
     * @aa add by chaijian 2018-08-13
     */
    @Select({"select DISTINCT phase_id from cefa_edu_user_organ_role uor where uor.state=0 and uor.grade_id=#{gradeId}"})
    List<Integer> getPhaseIdListByGradeId(Integer gradeId);

    /**
     * @aa 添加教师
     * @aa add by chaijian 2018-08-14
     */
    @Insert({"INSERT into cefa_edu_user_organ_role(user_id,school_id,phase_id,grade_id,class_id,course_id,role_type)values(#{userId},#{schoolId},#{phaseId},#{gradeId},#{classId},#{courseId},#{roleType})"})
    int saveUserOrganRoleForInstructor (Map<String,Object> paramMap);
    /**
     * @aa 添加班主任
     * @aa add by chaijian 2018-08-14
     */
    @Insert({"INSERT into cefa_edu_user_organ_role(user_id,school_id,phase_id,grade_id,class_id,role_type)values(#{userId},#{schoolId},#{phaseId},#{gradeId},#{classId},#{roleType})"})
    int saveUserOrganRoleForDirector (Map<String,Object> paramMap);

    /**
     * @aa 添加年级管理者
     * @aa add by chaijian 2018-08-13
     */
    @Insert({"INSERT into cefa_edu_user_organ_role(user_id,school_id,phase_id,grade_id,role_type,duties)values(#{userId},#{schoolId},#{phaseId},#{gradeId},#{roleType},#{duties})"})
    int saveUserOrganRoleForGradeManager(Map<String,Object> paramMap);
    /**
     * @aa 添加教育阶段管理者
     * @aa add by chaijian 2018-08-13
     */
    @Insert({"INSERT into cefa_edu_user_organ_role(user_id,school_id,phase_id,role_type,duties)values(#{userId},#{schoolId},#{phaseId},#{roleType},#{duties})"})
    int saveUserOrganRoleForPhaseManager(Map<String,Object> paramMap);
    /**
     * @aa 根据userId、roleType、courseId判断userId指定的用户是否已分配了教师角色(一个角色对应一条数据)
     * @aa add by chaijian 2018-08-13
     */
    @Select({"select count(uor.id) from cefa_edu_user_organ_role uor where uor.state=0 and uor.user_id=#{userId} and uor.role_type=#{roleType} and uor.course_id=#{courseId}"})
    int getCountRecordWithUserIdAndRoleTypeAndCourseId(@Param("userId")Integer userId,@Param("roleType")Integer roleType,@Param("courseId")Integer courseId);


    /**
     * @aa 根据userId、roleType判断userId指定的用户是否已分配了roleType对应的角色(一个角色对应一条数据)
     * @aa add by chaijian 2018-08-13
     */
    @Select({"select count(uor.id) from cefa_edu_user_organ_role uor where uor.state=0 and uor.user_id=#{userId} and uor.role_type=#{roleType}"})
    int getCountRecordWithUserIdAndRoleType(@Param("userId")Integer userId,@Param("roleType")Integer roleType);

    /**
     * @aa 添加用户组织机构角色关系数据
     * @aa add by chaijian 2018-08-13
     */
    @Insert({"insert into cefa_edu_user_organ_role(user_id,school_id,phase_id,grade_id,role_type) values ${values}"})
    int saveUserOrganRole(@Param("values") String values);


    @Update("update cefa_edu_user_organ_role set state=1  WHERE grade_id = #{gradeId} and role_type in(3,4,5,7) and state=0")
    void deleteUserOrganRoleByGradeId(@Param("gradeId") Integer gradeId);

    @Select("select id,user_id,school_id,phase_id,grade_id,class_id,course_id,role_type,state,duties from cefa_edu_user_organ_role where user_id=#{userId} and role_type=#{roleType} and state=0")
    @Results(id = "userOrganRoleMap", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "schoolId", column = "school_id"),
            @Result(property = "phaseId", column = "phase_id"),
            @Result(property = "gradeId", column = "grade_id"),

            @Result(property = "classId", column = "class_id"),
            @Result(property = "courseId", column = "course_id"),
            @Result(property = "roleType", column = "role_type"),
            @Result(property = "state", column = "state"),

    })
    List<UserOrganRole> findUserOrganRoleByRoleAndUserId(@Param("userId") Integer userId,@Param("roleType") Integer roleType);

    /**
     * 修改职务
     *
     * @param userId
     * @param roleType
     * @param duties
     */
    @Update("update cefa_edu_user_organ_role set duties=#{duties}  WHERE user_id = #{userId} and role_type=#{roleType}")
    void updateOrganRelationByIdAndRoleType(@Param("userId") Integer userId, @Param("roleType") Integer roleType, @Param("duties") String duties);


    @Select("select user_id as userId,school_id as schoolId,phase_id as phaseId \n" +
            "from cefa_edu_user_organ_role where #{userId}=1 group by school_id,phase_id order by school_id")
    List<Framework> findPhaseUnderSchool(@Param("userId") Integer userId);


    /**
     * @param userId
     * @return s查询用户所在的学校ids
     */
    @Select("select school_id from cefa_edu_user_organ_role where user_id=#{userId} and state=0 GROUP BY school_id")
    List<Integer> findSchoolIds(@Param("userId") Integer userId);

    /**
     * @param userId
     * @param schoolId
     * @return s根据用户id和schoolId查找用户拥有的教育阶段ids
     */
    @Select("select phase_id from cefa_edu_user_organ_role where user_id=#{userId} and school_id=#{schoolId} and state=0 group by phase_id")
    List<Integer> findPhaseIds(@Param("userId") Integer userId, @Param("schoolId") Integer schoolId);


    /**
     * @param schoolId
     * @return s根据学校id查询当前学校下所有的教育阶段
     */
    @Select("select id,phase_name as phaseName,sort,grade_count as gradeCount,school_id as \n" +
            "schoolId from cefa_edu_phase where school_id=#{schoolId} and state=0 ORDER BY sort ")
    List<Phase> findPhases(@Param("schoolId") Integer schoolId);


    /**
     * @param schoolId
     * @return s根据学校id查询此学校的教师人数
     */
    @Select("select count(a.user_id) from (\n" +
            "select user_id from  cefa_edu_user_organ_role where school_id=#{schoolId} and role_type in (4,5) and state=0 group by user_id) a")
    Integer getTeacherNumber(@Param("schoolId") Integer schoolId);


    /**
     * @param schoolId
     * @return s根据学校id查询此学校的学生人数
     */
    @Select("select count(a.user_id) from (select user_id from  cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and role_type=7 and state=0  group by user_id) a\n")
    Integer getSchoolStudentNumber(@Param("schoolId") Integer schoolId);


    /**
     * @param phaseId
     * @param schoolId
     * @return s根据学校id和教育阶段id查询教育阶段的教师人数
     */
    @Select("select count(user_id) from \n" +
            "(select user_id from cefa_edu_user_organ_role where school_id=#{schoolId} and phase_id=#{phaseId}  \n" +
            "and role_type in (4,5) and state=0 group by user_id) a")
    Integer getPhaseTeacherNumber(@Param("phaseId") Integer phaseId, @Param("schoolId") Integer schoolId);

    /**
     * @param phaseId
     * @param schoolId
     * @return s根据学校id和教育阶段id查询教育阶段的学生人数
     */
    @Select("select count(user_id) from \n" +
            "(select user_id from cefa_edu_user_organ_role where school_id=#{schoolId} and phase_id=#{phaseId}  \n" +
            "and role_type =7 and state=0 group by user_id) a")
    Integer getPhaseStudentNumber(@Param("phaseId") Integer phaseId, @Param("schoolId") Integer schoolId);

    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @return s根据学校id、教育阶段id、年级id查询此年级下面老师的人数
     */
    @Select("select count(a.user_id) from \n" +
            "(select user_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and role_type in (4,5) and state=0\n" +
            "group by user_id)a")
    Integer getGradeTeacherNumber(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId);


    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @return s根据学校id、教育阶段id、年级id查询此年级下面学生的人数
     */
    @Select("select count(a.user_id) from \n" +
            "(select user_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and role_type=7 and state=0\n" +
            "group by user_id)a")
    Integer getGradeStudentNumber(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId);


    /**
     * @param phaseId
     * @param schoolId
     * @return s根据学校id和教育阶段id查询此教育阶段下面的所有的年级
     */
    @Select("select id,grade_name as gradeName,grade_date as gradeDate,school_id as schoolId,sort,type,count from \n" +
            "(select grade_id from cefa_edu_organ_relation where school_id=#{schoolId} and phase_id=#{phaseId} and state=0 and grade_id!=0 group by grade_id) a\n" +
            "left join cefa_edu_grade b on a.grade_id=b.id ORDER BY b.sort")
    List<Grade> findAllGradeAtPhase(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId);

    /**
     * @param userId
     * @param schoolId
     * @param phaseId
     * @return s根据用户id、学校id、 教育阶段id查询年级id
     */
    @Select("select grade_id from cefa_edu_user_organ_role \n" +
            "where user_id=#{userId} and school_id=#{schoolId} and phase_id=#{phaseId} and state=0 \n" +
            "group by grade_id")
    List<Integer> findGradeIdsByPhaseId(@Param("userId") Integer userId, @Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId);


    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @return s根据学校id、教育阶段id、年级id查询此年级下面所有的班级
     */
    @Select("select id,class_name as className,sort,school_id as schoolId,count,state from (\n" +
            "select class_id from cefa_edu_organ_relation \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id!=0 and state=0 \n" +
            "group by class_id ) a\n" +
            "left join cefa_edu_class b on a.class_id=b.id")
    List<Class> findAllClassAtGrade(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId);


    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @return s根据学校id、教育阶段id、年级id、班级id查询此班级下面教师的人数
     */
    @Select("select count(a.user_id) from \n" +
            "(select user_id from cefa_edu_user_organ_role\n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and role_type in (4,5) and state=0\n" +
            "group by user_id) a")
    Integer getClassTeacherNumber(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);


    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @return s根据学校id、教育阶段id、年级id、班级id查询此班级下面学生的人数
     */
    @Select("select count(a.user_id) from \n" +
            "(select user_id from cefa_edu_user_organ_role\n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and role_type=7 and state=0\n" +
            "group by user_id) a")
    Integer getClassStudentNumber(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);

    /**
     * @param userId
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @return s根据用户id、学校id、教育阶段id、年级id查询此用户拥有的class id
     */
    @Select("select class_id from cefa_edu_user_organ_role \n" +
            "where user_id=#{userId} and school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and state=0\n" +
            "group by class_id")
    List<Integer> findClassIdsByGradeId(@Param("userId") Integer userId, @Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId);


    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @return s查询班主任老师的信息
     */
    @Select("select b.id,b.name from \n" +
            "(select user_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and role_type=4 and state=0\n" +
            "group by user_id,course_id) a\n" +
            "left join cefa_edu_user b on a.user_id=b.id")
    List<Instructor> findDirectorOfClass(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId,
                                   @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);


    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @return 查询某一个班级的所有任课教师和这个任课老师所教授的课程
     */
    @Select("select b.id,b.name,b.user_name as userName,b.id_code as idCode,c.id as courseId,c.course_name as courseName from \n" +
            "(select user_id ,course_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and role_type in (4,5) and state=0\n" +
            "group by user_id,course_id) a\n" +
            "left join cefa_edu_user b on a.user_id=b.id\n" +
            "left join cefa_edu_course c on a.course_id=c.id")
    List<Instructor> findTeachersOfClass(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId,
                                         @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);


    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @return s查询一个班级的学生信息
     */
    @Select("select b.id,b.name,b.user_name as userName,b.id_code as idCode, a.grade_id gradeId from \n" +
            "(select user_id ,grade_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and role_type=7 and state=0\n" +
            "group by user_id) a\n" +
            "left join cefa_edu_user b on a.user_id=b.id")
    List<User> findStudentsOfClass1(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId,
                                   @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);



    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @return s查询一个班级的学生信息
     */
    @Select("select b.id,b.name,b.user_name as userName,b.id_code as idCode,a.phase_id as phaseId,c.phase_name as phaseName,a.grade_id as gradeId,\n" +
            "d.grade_name as gradeName,d.grade_date as gradeDate,a.class_id as classId,f.class_name as className\n" +
            "from (select user_id,phase_id,grade_id,class_id from cefa_edu_user_organ_role " +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and role_type=7 and state=0 \n" +
            "group by user_id) a\n" +
            "left join cefa_edu_user b on a.user_id=b.id\n" +
            "left join cefa_edu_phase c on a.phase_id=c.id\n" +
            "left join cefa_edu_grade d on a.grade_id=d.id\n" +
            "left join cefa_edu_class f on a.class_id=f.id")
    List<User> findStudentsOfClass(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId,
                                   @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);



    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @param studentId
     * @return s查询学生的所学的课程信息
     */
    @Select("select b.id,b.course_name as courseName,b.recording_name as recordingName\n" +
            " from cefa_edu_course b where grade_id in \n" +
            "(select grade_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and user_id=#{studentId} \n" +
            "and role_type=7 and state=0) and state=0 group by course_name")
    List<Course> findStudentCourse(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId,
                                   @Param("gradeId") Integer gradeId, @Param("classId") Integer classId, @Param("studentId") Integer studentId);


    /**
     * @param loginUserId
     * @return s根据用户id查询用户的信息
     */
    @Select("select id,name,user_name as userName from cefa_edu_user where id=#{loginUserId}")
    User findUserById(@Param("loginUserId") Integer loginUserId);

    /**
     * @param schoolId
     * @param loginUserId
     * @return s根据学校id、用户id查询用户的角色
     */
    @Select("select id,role_name_english as roleNameEnglish,type,role_name_chinese as roleNameChinese from cefa_edu_role\n" +
            "where id=(select role_type from cefa_edu_user_organ_role where school_id=#{schoolId} and user_id=#{loginUserId} order by role_type limit 1)")
    Role getUserRoleForScore(@Param("schoolId") Integer schoolId, @Param("loginUserId") Integer loginUserId);


    /**
     * @param score s保存学生成绩
     */
    @Insert("insert into \n" +
            "cefa_edu_score (course_id,user_id,level_id,student_name,education_stage_id,grade_id,education_stage_name,grade_name,\n" +
            "class_name,course_name,recording_name,examination_time,recording_time,recording_person,fraction)\n" +
            "VALUES(#{courseId},#{userId},#{classId},#{studentName},#{phaseId},#{gradeId},#{phaseName},#{gradeName},#{className},#{courseName}," +
            "#{recordingName},#{examinationTime},#{recordingTime},#{recordingPerson},#{fraction})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void saveStudentScore(com.cefa.edu.pojo.Score score);


    /**
     * @param schoolId
     * @return s查询所有的学生信息
     */
    @Select("select b.id,b.name,a.phase_id as phaseId,c.phase_name as phaseName,a.grade_id as gradeId,\n" +
            "d.grade_name as gradeName,d.grade_date as gradeDate,a.class_id as classId,f.class_name as className\n" +
            "from (select user_id,phase_id,grade_id,class_id from cefa_edu_user_organ_role where school_id=#{schoolId} and role_type=7 and state=0 group by user_id) a\n" +
            "left join cefa_edu_user b on a.user_id=b.id\n" +
            "left join cefa_edu_phase c on a.phase_id=c.id\n" +
            "left join cefa_edu_grade d on a.grade_id=d.id\n" +
            "left join cefa_edu_class f on a.class_id=f.id " +
            "ORDER BY c.sort,d.sort,f.id")
    List<StudentInformation> findAllStudentInfo(@Param("schoolId") Integer schoolId);


    /**
     * @param loginUserId
     * @param schoolId
     * @return
     */
    @Select("select user_id as userId,school_id as schoolId,phase_id as phaseId,grade_id as gradeId,class_id as classId,role_type as roleType\n" +
            "from cefa_edu_user_organ_role where school_id=#{schoolId} and user_id=#{loginUserId} and state=0 order by role_type")
    List<UserOrganRole> findRoleIdsOfUser(@Param("loginUserId") Integer loginUserId, @Param("schoolId") Integer schoolId);

    /**
     * @param schoolId
     * @return s
     */
    @Select("select user_id from cefa_edu_user_organ_role where school_id=#{schoolId} and role_type=7 and state=0 group by user_id")
    List<Integer> findAllStudentIds(@Param("schoolId") Integer schoolId);

    /**
     * @param schoolId
     * @param phaseId
     * @return
     */
    @Select("select user_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and role_type=7 and state=0 group by user_id")
    List<Integer> findAllStudentsIdsOfPhase(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId);

    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @return
     */
    @Select("select user_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and role_type=7 and state=0 group by user_id")
    List<Integer> findAllStudentsIdsOfGrade(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId);

    /**
     * @param schoolId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @return
     */
    @Select("select user_id from cefa_edu_user_organ_role \n" +
            "where school_id=#{schoolId} and phase_id=#{phaseId} and grade_id=#{gradeId} and class_id=#{classId} and role_type=7 and state=0 group by user_id\n")
    List<Integer> findAllStudentsIdsOfClass(@Param("schoolId") Integer schoolId, @Param("phaseId") Integer phaseId, @Param("gradeId") Integer gradeId, @Param("classId") Integer classId);

    /**
     * @param schoolId
     * @param phaseIds
     * @return s根据学校id, 教育阶段id查找此教育阶段的管理者
     */
    @Select("select a.duties,b.id,b.name,b.user_name as userName,b.id_code as idCode,c.role_name_chinese as roleNameChinese from \n" +
            "(select duties,user_id,role_type from cefa_edu_user_organ_role where state=0 and school_id=#{schoolId} \n" +
            "and phase_id in (${phaseIds}) and role_type in (2)\n" +
            "group by phase_id,role_type,user_id) a\n" +
            "left join cefa_edu_user b  on a.user_id=b.id\n" +
            "left join cefa_edu_role c on a.role_type=c.type group by b.id")
    List<ManagerRoleInfo> findPhaseManagers(@Param("schoolId") Integer schoolId, @Param("phaseIds") String phaseIds);

    /**
     * @param schoolId
     * @param phaseIds
     * @param gradeIds
     * @return 根据学校id, 教育阶段id, 年级id查找此年级的管理者
     */
    @Select("select a.duties,b.id,b.name,b.user_name as userName,b.id_code as idCode, c.role_name_chinese as roleNameChinese from \n" +
            "(select duties,user_id,role_type from cefa_edu_user_organ_role where state=0 and school_id=#{schoolId} \n" +
            "and phase_id in (${phaseIds}) and grade_id in (${gradeIds}) and role_type in (3) \n" +
            "group by user_id order by role_type) a\n" +
            "left join cefa_edu_user b  on a.user_id=b.id\n" +
            "left join cefa_edu_role c on a.role_type=c.type group by b.id")
    List<ManagerRoleInfo> findGradeManagers(@Param("schoolId") Integer schoolId, @Param("phaseIds") String phaseIds, @Param("gradeIds") String gradeIds);

    /**
     * @param schoolId
     * @param gradeId
     * @return s查找某一个年级下面的课程
     */
    @Select("select id,course_name as courseName,grade_id as gradeId,recording_name as recordingName \n" +
            "from cefa_edu_course where school_id=#{schoolId} and grade_id=#{gradeId} and state=0")
    List<Course> findCoursesOfGrade(@Param("schoolId") Integer schoolId, @Param("gradeId") Integer gradeId);

    /**
     * @param id
     * @return
     * s查找用户所在的学校的名字
     */
    @Select("select b.school_name from \n" +
            "(select school_id from cefa_edu_user where id=#{userId}) a\n" +
            "left join cefa_edu_school  b on a.school_id=b.id")
    String findSchoolName(@Param("userId") Integer id);

    @Update({"update cefa_edu_user_organ_role uor set uor.state=1 where uor.phase_id=#{phaseId}"})
    void deleteUserOrganRoleByPhaseId(Integer phaseId);

    @Update({"update cefa_edu_user_organ_role uor set uor.state=1 where uor.class_id=#{classId}"})
    void deleteUserOrganRoleByClassId(Integer classId);

    @Select({"<script>",
            "SELECT count(1) FROM cefa_edu_user_organ_role a left join cefa_edu_user b on a.user_id=b.id",
            "WHERE (b.id_code=#{idCode}) and a.role_type=#{roleType}",
            "<when test='roleType==1 or roleType==2'>",
            " and a.phase_id=#{levelId}",
            "</when>",
            "<when test='roleType==3'>",
            " and a.grade_id=#{levelId}",
            "</when>",
            "<when test='roleType==4 or roleType==6 or roleType==7'>",
            " and a.class_id=#{levelId}",
            "</when>",
            "<when test='roleType==5'>",
            " and a.class_id=#{levelId} and a.course_id=#{courseId}",
            "</when>",
            "</script>"})
    int getUserOrganRole(@Param("userName")String userName,@Param("idCode")String idCode,@Param("userId")Integer userId,@Param("roleType")Integer roleType,@Param("levelId")Integer levelId,@Param("courseId")Integer courseId);

    @Insert({"INSERT into cefa_edu_user_organ_role(user_id,school_id,phase_id,grade_id,class_id,course_id,role_type,duties)values(#{userId},#{schoolId},#{phaseId},#{gradeId},#{classId},#{courseId},#{roleType},#{duties})"})
    int saveUserOrganRoleAll(UserOrganRole userOrganRole);


    /**
     * @param schoolId
     * @param gradeId
     * @return s查询年级下面的课程
     */
    @Select("select id,course_name as courseName,sort,grade_id as gradeId,\n" +
            "school_id as schoolId,state,recording_name as recordingName \n" +
            "from cefa_edu_course where grade_id=#{gradeId} and school_id=#{schoolId} and state=0 GROUP BY courseName;")
    List<Course> searchCourses(@Param("schoolId") Integer schoolId, @Param("gradeId") Integer gradeId);

    /**
     * @aa 为超级管理者配置可以访问的学习阶段
     * @aa add by chaijian 2018-08-20
     */
    @Insert({"insert into cefa_edu_user_organ_role(user_id,school_id,phase_id,role_type) VALUES(#{userId},#{schoolId},#{phaseId},#{roleType})"})
    int saveUserOrganRoleForAdmin(@Param("userId") Integer userId, @Param("schoolId")Integer schoolId, @Param("phaseId")Integer phaseId, @Param("roleType")Integer roleType);

    /**
     * @param userId
     * @param phaseId
     * @param gradeId
     * @param classId
     * @param courseId
     * @param recordingName
     * @return
     * s查询此学生的成绩是否已经存在
     */
    @Select("select count(id) from cefa_edu_score where user_id=#{userId} and education_stage_id=#{phaseId} and \n" +
            "grade_id=#{gradeId} and level_id=#{classId} and course_id=#{courseId} and recording_name=#{recordingName}")
    Integer checkStudentScoreExist(@Param("userId")Integer userId, @Param("phaseId")Integer phaseId,
                                   @Param("gradeId")Integer gradeId, @Param("classId")Integer classId,
                                   @Param("courseId")Integer courseId, @Param("recordingName")String recordingName);

    @Update({"update cefa_edu_user_organ_role set course_id=#{courseId},role_type=#{roleType} where user_id=#{id} and class_id=#{levelId}"})
    Integer updateTeacherUserOrganRole(User user, Integer roleType,Integer levelId);


    @Update({"update cefa_edu_user_organ_role uor set uor.duties=#{duties}  WHERE uor.user_id = #{id} and uor.phase_id=#{levelId}"})
    Integer updateUserOrganRoleForPhaseManager(@Param("duties")String duties,@Param("id")Integer id,@Param("levelId")Integer levelId);

    @Update({"update cefa_edu_user_organ_role uor set uor.duties=#{duties}  WHERE uor.user_id = #{id} and uor.grade_id=#{levelId}"})
    Integer updateUserOrganRoleForGradeManager(@Param("duties")String duties,@Param("id")Integer id,@Param("levelId")Integer levelId);

    @Update({"update cefa_edu_user_organ_role uor set uor.course_id=#{courseId},uor.role_type=#{roleType} where uor.user_id=#{id} and uor.class_id=#{levelId}"})
    Integer updateUserOrganRoleForInstructor (@Param("courseId")Integer courseId,@Param("roleType")Integer roleType,@Param("id")Integer id,@Param("levelId")Integer levelId);
    @Update({"update cefa_edu_user_organ_role uor set uor.role_type=#{roleType} where uor.user_id=#{id} and uor.class_id=#{levelId}"})
    Integer updateUserOrganRoleForDirector(@Param("roleType")Integer roleType,@Param("id") Integer id,@Param("levelId") Integer levelId);

    @Select({"select uor.role_type  from cefa_edu_user_organ_role uor where uor.state=0 and uor.user_id=#{userId} and uor.school_id=#{schoolId} "})
    List<Integer> getRoleTypeByUserIdAndSchoolId(@Param("userId") Integer userId, @Param("schoolId") Integer schoolId);

    @Select({"select uor.course_id from cefa_edu_user_organ_role uor where uor.user_id=#{userId} and role_type=#{roleType} and class_id=#{levelId}"})
    List<Integer> getCourseIdList(@Param("userId")Integer userId, @Param("levelId") Integer levelId, @Param("roleType") Integer roleType);

    @Select({"select count(uor.id) from cefa_edu_user_organ_role uor where uor.course_id=#{courseId} and uor.state=0"})
    int getCountOfCourseFromUserOrganRoleWithCourseId(Integer courseId);

    @Select({"select DISTINCT uor.school_id from cefa_edu_user_organ_role uor where uor.role_type=#{roleType}"})
    List<Integer> getSchoolIdsBytRoleType(int roleType);

    @Select({"select uor.user_id userId,uor.school_id schoolId,uor.phase_id phaseId,uor.grade_id gradeId,uor.class_id classId,uor.course_id courseId,uor.role_type roleType,uor.duties duties from cefa_edu_user_organ_role uor where state=0 and uor.user_id=#{userId}"})
    List<Map<String,Object>> getUserOrganRoleByUserId(Integer userId);

    @Select({"select uor.course_id from cefa_edu_user_organ_role uor where uor.state=0 and uor.user_id=#{userId} and uor.grade_id =#{gradeId} and uor.school_id=#{schoolId}"})
    List<Integer> getCourseIdListWithUserIdAndGradeIdAndSchoolId(@Param("userId")Integer userId, @Param("gradeId") Integer gradeId, @Param("schoolId")Integer schoolId);

    class UserDao{
        public String updateUserOrganRoleWithLevelIdAndUser(User user, Integer roleType, Integer levelId){
            StringBuffer sqlSb=new StringBuffer("update cefa_edu_user_organ_role uor set ");
            //修改的用户为管理者 修改其职务属性值
            if(roleType==2||roleType==3){

                //教育阶段管理者：update cefa_edu_user_organ_role set duties=#{duties}  WHERE user_id = #{userId} and phase_id=#{levelId}
                //年级管理者：update cefa_edu_user_organ_role set duties=#{duties}  WHERE user_id = #{userId} and grade_id=#{levelId}
                sqlSb.append("uor.duties=#{duties} ");
                sqlSb.append("where ");
                sqlSb.append("uor.user_id=#{id} ");
                if (roleType==2){
                    sqlSb.append("and uor.phase_id=#{levelId}");
                }else {
                    sqlSb.append("and uor.grade_id=#{levelId}");
                }
            }

            //修改的用户为老师、班主任 修改其科目和角色属性值
            if(roleType==4||roleType==5){
                //update cefa_edu_user_organ_role set course_id=1,role_type=7 where user_id=22 and class_id=2
                if(user.getCourseId()!=null){
                    sqlSb.append("uor.course_id=#{courseId}, ");
                }
                sqlSb.append("uor.role_type=#{roleType} ");
                sqlSb.append("where ");
                sqlSb.append("uor.user_id=#{id} ");
                sqlSb.append("and uor.class_id=#{levelId}");
            }
            return sqlSb.toString();
        };

    }
}
